import uuid
import time
from typing import List

from sqlalchemy.orm import Session

import models
import networkModels


def get_user_by_name(db: Session, user_name: str):
    return db.query(models.User).filter(models.User.name == user_name).first()


def check_user(db: Session, user_name: str, user_password: str):
    return db.query(models.User).filter(models.User.name == user_name and models.User.password == user_password).first()


# 新建用户
def create_user(db: Session, request: networkModels.LoginRequest):
    db_user = models.User(name=request.name, password=request.password)
    db.add(db_user)
    db.commit()
    db.refresh(db_user)
    return db_user


def query_goods(db: Session, goods_id: str):
    result = db.execute(
        "select g.id,g.name,g.price,g.square_pic squarePic,g.description from m_goods g where id = " + goods_id)
    return result.first()


def search_goods(db: Session, keyword: str):
    result = db.execute(
        "select g.id,g.name,g.price,g.square_pic squarePic,g.description from m_goods g where g.name like '%" + keyword + "%'")
    return result.all()


def query_goods_by_category(db: Session, category_id: str):
    result = db.execute(
        "SELECT g.id,g.name,g.price,g.square_pic squarePic,g.description FROM m_goods_category gc,m_goods g WHERE gc.goods_id=g.id AND gc.category_id = " + category_id)
    return result.all()


def query_order(db: Session, order_id: str):
    # result = db.query(models.UserOrder).first()
    result = db.execute("SELECT * from m_user_order where id = '" + order_id + "'")
    user_order_result = result.first()

    order_id = user_order_result["id"]
    status = user_order_result["status"]
    create_time = user_order_result["create_time"]
    result = {"orderId": order_id, "status": status, "createTime": create_time}

    order_list = db.execute(
        "SELECT uo.id orderId,uo.status,g.id goodsId,g.name,g.price,g.square_pic squarePic,o.quantity FROM m_user_order uo,m_orders o,m_goods g WHERE uo.id=o.order_id AND o.goods_id=g.id AND o.order_id = '" + order_id + "'")

    result["list"] = order_list.all()

    return result


def query_orders(db: Session, user_id: int):
    # result = db.query(models.UserOrder).all()
    user_order_result = db.execute("SELECT * from m_user_order where user_id = " + user_id)
    user_order_result = user_order_result.all()
    result = []
    for i in range(0, len(user_order_result)):
        order_id = user_order_result[i]["id"]
        status = user_order_result[i]["status"]
        create_time = user_order_result[i]["create_time"]
        order_list = db.execute(
            "SELECT uo.id orderId,uo.status,g.id goodsId,g.name,g.price,g.square_pic squarePic,o.quantity FROM m_user_order uo,m_orders o,m_goods g WHERE uo.id=o.order_id AND o.goods_id=g.id AND o.order_id = '" + order_id + "'")
        result.append({'orderId': order_id, 'status': status, 'createTime': create_time, 'list': order_list.all()})

    return result


def query_category(db: Session):
    result = db.execute("SELECT * FROM m_category")
    return result.all()


def create_order(db: Session, user_id: int, simpleCartItemList: List[networkModels.SimpleCartItem]):
    order_id = str(uuid.uuid1())
    user_order = models.UserOrder(id=order_id, user_id=user_id, status=1, create_time=int(round(time.time() * 1000)))
    db.add(user_order)
    for simpleCartItem in simpleCartItemList:
        order = models.Order(id=str(uuid.uuid1()), goods_id=simpleCartItem.goodsId, order_id=order_id,
                             quantity=simpleCartItem.quantity)
        db.add(order)
    db.commit()
    return order_id


def delete_order(db: Session, order_id: str):
    db.execute("DELETE from m_orders where order_id = '" + order_id + "'")
    db.execute("DELETE from m_user_order where id = '" + order_id + "'")
    db.commit()
    return 0
